package com.quotes.io;

import java.io.*;
import java.util.*;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;

import com.quotes.datamodell.*;

public class ExcelHelper {
	private Logger logger = Logger.getLogger(getClass().toString());
	private Workbook workbook;
	private CellStyle dateCellStyle;
	private DataFormat dataFormat;
	
	public ExcelHelper() {
	}
	
	private void createCellStyle() {
		dateCellStyle = workbook.createCellStyle();
		dataFormat = workbook.createDataFormat();
		dateCellStyle.setDataFormat(dataFormat.getFormat("yyy-MM-dd"));
	}
	
	public void createNewWorkbook() {
		workbook = new HSSFWorkbook();
		createCellStyle();
	}
	
	public void loadWorkbookFromFile(String fileName) throws Exception {
		workbook = new HSSFWorkbook(new FileInputStream(fileName));
		createCellStyle();
	}
	
	public void saveExcelSheet(String fileName) {
		try {
			FileOutputStream out = new FileOutputStream(fileName);
			workbook.write(out);
			out.close();
		} catch(Exception e) { 
			logger.error("Could not write to excel file!", e);
		}
	}
	
	public void createSheet(String sheetName) {
		workbook.createSheet(sheetName);
	}
	
	public List<String> getSheetNames() {
		List<String> sheets = new ArrayList<String>();
		for (int i=0; i<workbook.getNumberOfSheets(); i++) {
			String sheetName = workbook.getSheetName(i);
			
			if (sheetName != null)
				sheets.add(workbook.getSheetName(i));
		}
		
		return sheets;
	}
	
	public void writeToSheet(String sheetName, DataTable table, int rowNum, int colNum) {
		writeHeaderRow(sheetName, rowNum, colNum, table.getColumns());
		
		for (int i=0; i<table.getNumberOfRows(); i++) {    
			writeDataRow(sheetName, rowNum + i + 1, colNum, table.getRow(i));
		 }
	}
	
	private void writeHeaderRow(String sheetName, int rowNum, int colNum, List<String> columns) {
		Sheet sheet = workbook.getSheet(sheetName);
		Row row = sheet.createRow(rowNum);
		
		for (short i=0; i<columns.size(); i++) {
			Cell cell = row.createCell((short)(i+colNum));
			cell.setCellValue(columns.get(i));
		}
	}
	
	public DataTable readFromSheet(String sheetName) {
		DataTable table = new DataTable();
		Sheet sheet = workbook.getSheet(sheetName);
		FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
				
		for (int rowNum=sheet.getFirstRowNum(); rowNum<=sheet.getLastRowNum(); rowNum++) {
			Row excelRow = sheet.getRow(rowNum);

			DataRow dataRow = new DataRow();
			for (short colNum=excelRow.getFirstCellNum(); colNum<excelRow.getLastCellNum(); colNum++) {
				Cell cell = excelRow.getCell(colNum);
				
				if (cell == null) {
					dataRow.addCell("");
				} else {
					if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
						if (DateUtil.isCellDateFormatted(cell)) {
							dataRow.addCell(cell.getDateCellValue());
						} else {
							dataRow.addCell(cell.getNumericCellValue());
						}
					} else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
						CellValue cellValue = evaluator.evaluate(cell);
						dataRow.addCell(cellValue.getNumberValue());
					}
					else {
						dataRow.addCell(cell.toString());
					}
				}
			}
			
			table.addRow(dataRow);
		}
		
		return table;
	}
	
	private void writeDataRow(String sheetName, int rowNum, int colNum, DataRow dataRow) {
		Sheet sheet = workbook.getSheet(sheetName);
		Row excelRow = sheet.createRow(rowNum);
		
		for (int i=0; i<dataRow.getNumberOfCells(); i++) {
			Cell cell = excelRow.createCell((short)(i+colNum));
			
			Object o = dataRow.getValue(i);
			if (o instanceof Date) {
				cell.setCellStyle(dateCellStyle);
				cell.setCellValue((Date)dataRow.getValue(i));
			} else if (o instanceof Double) {
				cell.setCellValue((Double)dataRow.getValue(i));
			} else {
				cell.setCellValue(dataRow.getValue(i).toString());
			}
		}
	}
}
